using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading;
using System.Xml;
using Microsoft.SqlServer.Dac;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.Diagnostics;
using Microsoft.WindowsAzure.ServiceRuntime;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;

namespace AzureSQLBackup
{
    public class WorkerRole : RoleEntryPoint
    {
        private CloudStorageAccount StorageAccount;
        private string inputXML = string.Empty;
        private LogDataContext logContext;

        public override void Run()
        {
            // This is a sample worker implementation. Replace with your logic.
            Trace.TraceInformation("AzureSQLBAckup entry point called", "Information");

            StorageAccount = CloudStorageAccount.Parse(CloudConfigurationManager.GetSetting("DataConnectionString"));
            int sleepingTime;
            if(!int.TryParse(CloudConfigurationManager.GetSetting("SleepingTime"),out sleepingTime))
            {
                // Default time 60 minutes
                sleepingTime = 60;
            }
            logContext = new LogDataContext(StorageAccount.CreateCloudTableClient());
            while (true)
            {
                inputXML = GetInputXML();
                if (inputXML.Trim().Length > 0)
                {
                    BackupSQLAzureDatabase();

                    Thread.Sleep(1000 * 60 * sleepingTime); // Every sleepingTime minutes
                }
                Thread.Sleep(1000 * 60);
                Trace.TraceInformation("Working", "Information");
            }
        }

        /// <summary>
        /// Start Backing up the SQL Azure DB
        /// </summary>
        /// <returns></returns>
        public bool BackupSQLAzureDatabase()
        {
            logContext.SaveEvent("Starting the Backup Process");

            if (inputXML.Trim().Length > 0)
            {

                XmlDocument doc = new XmlDocument();
                doc.LoadXml(inputXML);

                try
                {
                    foreach (XmlNode node in doc.DocumentElement.SelectNodes("Database"))
                    {
                        #region Get Values from XML
                        DatabaseInfo databaseInfo = new DatabaseInfo();

                        databaseInfo.ServerName = (node.Attributes["ServerName"] != null ? node.Attributes["ServerName"].Value : string.Empty);
                        databaseInfo.DatabaseName = (node.Attributes["DatabaseName"] != null ? node.Attributes["DatabaseName"].Value : string.Empty);
                        databaseInfo.UseWindowsAuth = (node.Attributes["UseWindowsAuth"] != null ? Convert.ToBoolean(node.Attributes["UseWindowsAuth"].Value) : false);
                        databaseInfo.LoginId = (node.Attributes["LoginId"] != null ? node.Attributes["LoginId"].Value : string.Empty);
                        databaseInfo.Password = (node.Attributes["Password"] != null ? node.Attributes["Password"].Value : string.Empty);
                        databaseInfo.BackupFileName = (node.Attributes["BackupFileName"] != null ? node.Attributes["BackupFileName"].Value.Replace("@Date", DateTime.Now.ToString("ddMMMMyyyy")).Replace("@Time", DateTime.Now.ToString("hhmmss")) : "Backup" + databaseInfo.DatabaseName + DateTime.Now.ToString("ddMMMyyyy hhmm"));
                        databaseInfo.BackupScheduledPeriod = (node.Attributes["BackupScheduledPeriod"] != null ? (BackupPeriod)Enum.Parse(typeof(BackupPeriod), node.Attributes["BackupScheduledPeriod"].Value) : BackupPeriod.Daily);
                        databaseInfo.BackupScheduledTime = (node.Attributes["BackupScheduledTime"] != null ? Convert.ToDateTime(node.Attributes["BackupScheduledTime"].Value) : DateTime.Today);
                        databaseInfo.LastBackupDateTime = (node.Attributes["LastBackupDateTime"].Value.Trim().Length == 0 ? System.DateTime.Now : Convert.ToDateTime(node.Attributes["LastBackupDateTime"].Value));
                        databaseInfo.ExportTo = (node.Attributes["ExportTo"] != null ? (ExportTo)Enum.Parse(typeof(ExportTo), node.Attributes["ExportTo"].Value) : ExportTo.FileSystem);
                        databaseInfo.ExportFileDirectory = (node.Attributes["ExportFileDirectory"] != null ? node.Attributes["ExportFileDirectory"].Value.Replace("@Date", DateTime.Now.ToString("ddMMMMyyyy")).Replace("@Time", DateTime.Now.ToString("hhmmss")) : string.Empty);
                        databaseInfo.ExportFileDirectory = (databaseInfo.ExportFileDirectory != string.Empty ? (databaseInfo.ExportFileDirectory.EndsWith(@"\") == true ? databaseInfo.ExportFileDirectory : databaseInfo.ExportFileDirectory + @"\") : @"C:\SQLAzureBackup");
                        databaseInfo.TrustServerCertificate = (node.Attributes["TrustServerCertificate"] != null ? Convert.ToBoolean(node.Attributes["TrustServerCertificate"].Value) : false);
                        databaseInfo.UseSSL = (node.Attributes["UseSSL"] != null ? Convert.ToBoolean(node.Attributes["UseSSL"].Value) : false);
                        databaseInfo.StorageAccountName = (node.Attributes["StorageAccountName"] != null ? node.Attributes["StorageAccountName"].Value : string.Empty);
                        databaseInfo.BlobContainerName = (node.Attributes["BlobContainerName"] != null ? node.Attributes["BlobContainerName"].Value : string.Empty);
                        databaseInfo.BlobDefaultEndpointsProtocol = (node.Attributes["BlobDefaultEndpointsProtocol"] != null ? node.Attributes["BlobDefaultEndpointsProtocol"].Value : string.Empty);
                        databaseInfo.StorageAccessKey = (node.Attributes["StorageAccessKey"] != null ? node.Attributes["StorageAccessKey"].Value : string.Empty);
                        #endregion

                        logContext.SaveEvent("Starting Process for " + databaseInfo.DatabaseName + "@" + databaseInfo.ServerName);

                        bool IsProceed = false;
                        #region Validation
                        if (databaseInfo.BackupScheduledPeriod == BackupPeriod.Daily)
                        {
                            logContext.SaveEvent("Validation \n LastBackupDateTime : " + databaseInfo.LastBackupDateTime.ToString("dd-MMM-yyyy") + " <= " + System.DateTime.Now.AddDays(-1).ToString("dd-MMM-yyyy") +
                                                        "\n BackupScheduledTime : " + databaseInfo.BackupScheduledTime.TimeOfDay.ToString() + " <= " + System.DateTime.Now.TimeOfDay.ToString());

                            if ((Convert.ToDateTime(databaseInfo.LastBackupDateTime.ToString("dd-MMM-yyyy")) <= Convert.ToDateTime(System.DateTime.Now.ToString("dd-MMM-yyyy")).AddDays(-1)) &&
                                (databaseInfo.BackupScheduledTime.TimeOfDay <= System.DateTime.Now.TimeOfDay))
                            {
                                IsProceed = true;
                            }
                        }
                        else if (databaseInfo.BackupScheduledPeriod == BackupPeriod.Weekly)
                        {
                            logContext.SaveEvent("Validation \n LastBackupDateTime : " + databaseInfo.LastBackupDateTime.ToString("dd-MMM-yyyy") + " <= " + System.DateTime.Now.AddDays(-7).ToString("dd-MMM-yyyy") +
                                             "\n BackupScheduledTime : " + databaseInfo.BackupScheduledTime.TimeOfDay.ToString() + " <= " + System.DateTime.Now.TimeOfDay.ToString());

                            if ((Convert.ToDateTime(databaseInfo.LastBackupDateTime.ToString("dd-MMM-yyyy")) <= Convert.ToDateTime(System.DateTime.Now.ToString("dd-MMM-yyyy")).AddDays(-7)) &&
                               (databaseInfo.BackupScheduledTime.TimeOfDay <= System.DateTime.Now.TimeOfDay))
                            {
                                IsProceed = true;
                            }
                        }
                        else if (databaseInfo.BackupScheduledPeriod == BackupPeriod.Monthly)
                        {
                            logContext.SaveEvent("Validation \n LastBackupDateTime : " + databaseInfo.LastBackupDateTime.ToString("dd-MMM-yyyy") + " <= " + System.DateTime.Now.AddMonths(-1).ToString("dd-MMM-yyyy") +
                                             "\n BackupScheduledTime : " + databaseInfo.BackupScheduledTime.TimeOfDay.ToString() + " <= " + System.DateTime.Now.TimeOfDay.ToString());
                            if ((Convert.ToDateTime(databaseInfo.LastBackupDateTime.ToString("dd-MMM-yyyy")) <= Convert.ToDateTime(System.DateTime.Now.ToString("dd-MMM-yyyy")).AddMonths(-1)) &&
                               (databaseInfo.BackupScheduledTime.TimeOfDay <= System.DateTime.Now.TimeOfDay))
                            {
                                IsProceed = true;
                            }
                        }
                        #endregion

                        if (IsProceed == true)
                        {
                            logContext.SaveEvent("Backup Started for database " + databaseInfo.DatabaseName + "@" + databaseInfo.ServerName + " at " + DateTime.Now.ToString("ddMMMMyyyy HHMMSS"));
                            bool isBackupSuccess = false;
                            #region Backup Process
                            switch (databaseInfo.ExportTo)
                            {
                                case ExportTo.FileSystem:
                                    isBackupSuccess = BackupToFileSystem(databaseInfo);
                                    break;
                                case ExportTo.Blob:
                                    isBackupSuccess = BackupToBlob(databaseInfo);
                                    break;
                            }
                            #endregion

                            logContext.SaveEvent("Backup Completed for database '" + databaseInfo.DatabaseName + "' on server '" + databaseInfo.ServerName + "' at " + DateTime.Now.ToString("ddMMMMyyyy HHMMSS"));
                            if (isBackupSuccess)
                            {
                                node.Attributes["LastBackupDateTime"].Value = System.DateTime.Now.ToString("dd-MMM-yyyy hh:mm tt");
                                PutInputXML(doc.InnerXml);
                            }
                        }
                        else
                        {
                            logContext.SaveEvent("Validation failed for database " + databaseInfo.DatabaseName + "@" + databaseInfo.ServerName);
                        }
                    }

                    return true;
                }
                catch (Exception ex)
                {
                    logContext.SaveError(String.Format("Error Occured : {0} at {1}", ex.Message, System.DateTime.Now.ToString("dd-MMM-yyyy hh:mm")), ex.Message + "\\" + ex.StackTrace);
                }
                return false;
            }
            else
            {
                logContext.SaveError("XML File does not found", "Checking File Exist");
            }
            logContext.SaveEvent("Ending the Backup Process");
            return false;
        }

        /// <summary>
        /// If the user choice is backing up to Blob container
        /// </summary>
        /// <param name="databaseInfo"></param>
        /// <returns></returns>
        private bool BackupToBlob(DatabaseInfo databaseInfo)
        {
            try
            {
                string connection = this.GetServerConnection(databaseInfo);
                DacServices dacStore = null;

                // Get a DacStore for the current connection
                dacStore = new DacServices(connection);

                string backupFileName = databaseInfo.DatabaseName + "_" + System.DateTime.Now.ToString("dd-MMM-yyyy hhmmss") + ".bacpac";
                FileStream fileStream = File.Create(backupFileName);
                dacStore.ExportBacpac(fileStream, databaseInfo.DatabaseName);
                fileStream.Close();

                // read account configuration settings
                string strConnectionString = "DefaultEndpointsProtocol=" + databaseInfo.BlobDefaultEndpointsProtocol + ";AccountName=" + databaseInfo.StorageAccountName + ";AccountKey=" + databaseInfo.StorageAccessKey;
                var storageAccount = CloudStorageAccount.Parse(strConnectionString);

                // create blob container for backup data
                var blobStorage = storageAccount.CreateCloudBlobClient();

                logContext.SaveEvent(string.Format("Checking blob to save the backup file, blob container name: {0}", databaseInfo.BlobContainerName));
                var container = blobStorage.GetContainerReference(databaseInfo.BlobContainerName);
                if (container.CreateIfNotExists())
                {
                    container.SetPermissions(new BlobContainerPermissions { PublicAccess = BlobContainerPublicAccessType.Off });
                }

                CloudBlockBlob blob = container.GetBlockBlobReference(databaseInfo.BackupFileName);

                blob.Metadata["Server"] = databaseInfo.ServerName;
                blob.Metadata["Database"] = databaseInfo.DatabaseName;
                blob.Metadata["FileName"] = databaseInfo.BackupFileName;
                blob.Metadata["BackupDate"] = DateTime.Now.ToString("dd-MMM-yyyy HH:MM");

                Stream backUpFileStream = File.OpenRead(backupFileName);
                blob.UploadFromStream(backUpFileStream);
                backUpFileStream.Close();
                System.IO.File.Delete(backupFileName);


                return true;
            }
            catch (IOException ioex)
            {
                logContext.SaveError("Error (IOException) accessing backup file", ioex.Message + "\n" + ioex.StackTrace);
            }
            catch (DacServicesException dacex)
            {
                logContext.SaveError("Error (DacException) when backup", dacex.Message + "\n" + dacex.StackTrace);
            }
            catch (Exception ex)
            {
                logContext.SaveError("Error when backup", ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
            }
            return false;
        }

        /// <summary>
        /// Backing up to the file system
        /// </summary>
        /// <param name="databaseInfo"></param>
        /// <returns></returns>
        private bool BackupToFileSystem(DatabaseInfo databaseInfo)
        {
            try
            {
                string connection = this.GetServerConnection(databaseInfo);
                DacServices dacStore = null;

                // Get a DacStore for the current connection
                dacStore = new DacServices(connection);

                string backupFileName = string.Empty;

                backupFileName = Path.Combine(databaseInfo.ExportFileDirectory, databaseInfo.BackupFileName);
                if (!Directory.Exists(databaseInfo.ExportFileDirectory))
                    Directory.CreateDirectory(databaseInfo.ExportFileDirectory);

                FileStream fileStream = File.Create(backupFileName);
                dacStore.ExportBacpac(fileStream, databaseInfo.DatabaseName);
                fileStream.Close();
                return true;
            }
            catch (IOException ioex)
            {
                logContext.SaveError("Error (IOException) accessing backup file", ioex.Message + "\n" + ioex.StackTrace);
            }
            catch (DacServicesException dacex)
            {
                logContext.SaveError("Error (DacException) when backup", dacex.Message + "\n" + dacex.StackTrace);
            }
            catch (Exception ex)
            {
                logContext.SaveError("Error when backup", ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
            }
            return false;
        }

        /// <summary>
        /// Read the xml configuration for databases info
        /// </summary>
        /// <returns></returns>
        public string GetInputXML()
        {
            try
            {
                CloudStorageAccount inputStorageAccount;

                inputStorageAccount = CloudStorageAccount.Parse(CloudConfigurationManager.GetSetting("InputXMLBlobConnectionString"));

                var blobClient = inputStorageAccount.CreateCloudBlobClient();

                blobClient.RetryPolicy = new Microsoft.WindowsAzure.Storage.RetryPolicies.LinearRetry(TimeSpan.Zero, 4);

                var blobContainer = blobClient.GetContainerReference(CloudConfigurationManager.GetSetting("InputXMLBlobContainerName"));
                var blob = blobContainer.GetBlobReferenceFromServer("SQLAzureBackupData.xml");
                string result = "";
                using (var stream = blob.OpenRead())
                {
                    using (StreamReader reader = new StreamReader(stream))
                    {
                        result = reader.ReadToEnd();
                    }
                }

                return result;
            }
            catch (Exception Ex)
            {
                logContext.SaveError("Getting Input XML cause issue. The error: " + Ex.Message + " at " + DateTime.Now.ToString("ddMMMMyyyy HHMMSS"));
            }
            return string.Empty;
        }

        /// <summary>
        /// Build SQL server connection string
        /// </summary>
        /// <param name="databaseInfo"></param>
        /// <returns></returns>
        private string GetServerConnection(DatabaseInfo databaseInfo)
        {
            SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();

            sqlConnectionStringBuilder.DataSource = databaseInfo.ServerName;

            sqlConnectionStringBuilder.TrustServerCertificate = databaseInfo.TrustServerCertificate;

            if (!string.IsNullOrEmpty(databaseInfo.DatabaseName))
                sqlConnectionStringBuilder.InitialCatalog = databaseInfo.DatabaseName;

            sqlConnectionStringBuilder.Encrypt = databaseInfo.UseSSL;

            if (databaseInfo.UseWindowsAuth == false)
            {
                if (databaseInfo.LoginId == null)
                {
                    throw new ArgumentException("User name must be specified unless Windows Authentication (-E) is specified.");
                }
                if (databaseInfo.Password == null)
                {
                    throw new InvalidOperationException("User password must be specified unless Windows Authentication (-E) is specified.");
                }
                sqlConnectionStringBuilder.IntegratedSecurity = false;
                sqlConnectionStringBuilder.UserID = databaseInfo.LoginId;
                sqlConnectionStringBuilder.Password = databaseInfo.Password;
            }
            else
            { // To do
                sqlConnectionStringBuilder.IntegratedSecurity = true;
            }

            sqlConnectionStringBuilder.ConnectTimeout = 60;
            sqlConnectionStringBuilder.MinPoolSize = 5;
            sqlConnectionStringBuilder.MaxPoolSize = 50;        // According to Azure team there is NO max limit on connections.  It all comes down to CPU and IO usage.  They said setting this to 100 would be fine.
            sqlConnectionStringBuilder.Pooling = true;

            // To do
            //// If we think we are talking to Azure we should turn on encryption if the user didn't set it
            //if ((this.azureEdition != AzureEdition.Default || this.azureSize != -1) && this.useSSL == false)
            //{
            sqlConnectionStringBuilder.Encrypt = true;
            //}

            return sqlConnectionStringBuilder.ToString();
        }

        /// <summary>
        /// Update the XML configuration file
        /// </summary>
        /// <param name="xmlData"></param>
        public void PutInputXML(string xmlData)
        {
            try
            {
                CloudStorageAccount storageAccount;
                CloudBlobClient blobClient;

                storageAccount = CloudStorageAccount.Parse(CloudConfigurationManager.GetSetting("InputXMLBlobConnectionString"));

                blobClient = storageAccount.CreateCloudBlobClient();
                blobClient.RetryPolicy = new Microsoft.WindowsAzure.Storage.RetryPolicies.LinearRetry(TimeSpan.Zero, 4);

                CloudBlobContainer blobContainer = blobClient.GetContainerReference(CloudConfigurationManager.GetSetting("InputXMLBlobContainerName"));

                var blob = blobContainer.GetBlobReferenceFromServer("SQLAzureBackupData.xml");
                byte[] buffer = ASCIIEncoding.ASCII.GetBytes(xmlData);
                MemoryStream data = new MemoryStream(buffer);
                blob.DeleteIfExists();
                blob.UploadFromStream(data);
                data.Close();
            }
            catch (Exception Ex)
            {
                logContext.SaveError("Getting Input XML cause issue. The error: " + Ex.Message + " at " + DateTime.Now.ToString("ddMMMMyyyy HHMMSS"));
            }
        }

        public override bool OnStart()
        {
            // Set the maximum number of concurrent connections 
            ServicePointManager.DefaultConnectionLimit = 12;

            // For information on handling configuration changes
            // see the MSDN topic at http://go.microsoft.com/fwlink/?LinkId=166357.

            return base.OnStart();
        }
    }
}
